﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CalculeazaStocLunar]
	@id_Medicament int,
	@luna int
AS
BEGIN
	DECLARE @cantitate_maxima_vanduta float = 0
	DECLARE @cantitate_maxima_vanduta_an_anterior float = NULL
	DECLARE @stocCurent float = 0
	DECLARE @stocLunar float
	
	DECLARE @dataCurenta datetime = GetDate()
	DECLARE @dataVerificareStoc datetime = GetDate()
	DECLARE @numarulDeZileAleLuni int
	DECLARE @lunaDeInteres datetime = CONVERT(nvarchar(4),YEAR(@dataCurenta))+ '-' + CONVERT(nvarchar(2),@luna) + '-01 00:00'
	
	SELECT @numarulDeZileAleLuni = DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(@lunaDeInteres), @lunaDeInteres)) - 1) 
	DECLARE @dataInceput datetime = DateAdd(M,-1,@lunaDeInteres)
	DECLARE @dataAnAnterior datetime = DATEADD(YY,-1,@lunaDeInteres)
	DECLARE @dateInceputAnAnterior datetime = DATEADD(YY,-1,@dataInceput)
	
	SELECT 
		@cantitate_maxima_vanduta = max(Cantitate) 
	FROM 
		Arhiva 
	WHERE 
		Id_Medicament = @id_Medicament AND
		DATA >= @dataInceput AND
		DATA <= @lunaDeInteres
	
	IF @cantitate_maxima_vanduta IS NULL
	BEGIN
		SET @cantitate_maxima_vanduta = 0
	END
	
	SELECT 
		@cantitate_maxima_vanduta_an_anterior = max(Cantitate) 
	FROM 
		Arhiva 
	WHERE 
		Id_Medicament = @id_Medicament AND
		DATA >= @dateInceputAnAnterior AND
		DATA <= @dataAnAnterior
	
	IF @cantitate_maxima_vanduta_an_anterior IS NOT NULL
	BEGIN
		SET @cantitate_maxima_vanduta = (@cantitate_maxima_vanduta + @cantitate_maxima_vanduta_an_anterior) / 2;
	END
	
	SET @stocLunar = @cantitate_maxima_vanduta * @numarulDeZileAleLuni

	DECLARE @stocCritic FLOAT
	EXEC CalculeazaStocCritic @id_Medicament,4,@stocCritic OUTPUT
	
	IF @stocCritic IS NULL
	BEGIN
		SET @stocCritic = 0
	END
	
	SET @dataVerificareStoc = DATEADD(M,1,@dataCurenta)

	SELECT @stocCurent = sum(Cantitate_Actuala)
	FROM Stoc 
	WHERE Id_Medicament = @id_Medicament AND
		  Data_Intrare <= @dataVerificareStoc AND
		  Data_Expirare > @dataVerificareStoc
	
	SET @stocLunar = @stocLunar + @stocCritic - @stocCurent

	if @stocLunar is null or @stocLunar < 0
		set @stocLunar = 0

	select convert(decimal,@stocLunar)
END
